<?php
namespace app\lib;

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\IOFactory;

class ExportExcel
{

    private $newExcel = null;
    private $objSheet = null;
    private $sheetTitle = null;
    private $format = null;
    private $isDown = true;

    public function __construct($sheetTitle='',$format='Xls')
    {
        $this->newExcel = new Spreadsheet();  //创建一个新的excel文档
        $this->objSheet = $this->newExcel->getActiveSheet();  //获取当前操作sheet的对象
        $this->sheetTitle = $sheetTitle;
        $this->format = $format;
        $this->objSheet->setTitle($this->sheetTitle);  //设置当前sheet的标题
    }


    public function num2Letter($num) {
        $num = intval($num);
        if ($num <= 0)
        {
            return false;
        }
        $letterArr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
        $letter = '';
        do {
            $key = ($num - 1) % 26;
            $letter = $letterArr[$key] . $letter;
            $num = floor(($num - $key) / 26);
        } while ($num > 0);
        return $letter;
    }

    public function exportExcel($arr_title, $arr_data)
    {
        array_unshift($arr_data, $arr_title);
        $num = count($arr_title);
        for ($i=1;$i<=$num;$i++)
        {
            $num2Letter = $this->num2Letter($num);
            $this->newExcel->getActiveSheet()->getColumnDimension($num2Letter)->setAutoSize(true);
        }
        foreach ($arr_data as $key=>$value)
        {
            $key++;
            $value = array_values($value);
            for ($i=1;$i<=$num;$i++)
            {
                $num2Letter = $this->num2Letter($i);
                $num2Letter.=$key;
                $value_v = isset($value[$i-1]) ? $value[$i-1] : '';
                $this->objSheet->setCellValueExplicit($num2Letter, $value_v,'s');
            }
        }
        $this->downloadExcel();
    }

    //公共文件，用来传入xls并下载
    function downloadExcel()
    {
        // $format只能为 Xlsx 或 Xls
        if ($this->format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($this->format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }

        header("Content-Disposition: attachment;filename="
            . $this->sheetTitle . date('Y-m-d') . '.' . strtolower($this->format));
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($this->newExcel, $this->format);
        $objWriter->save('php://output');
        //通过php保存在本地的时候需要用到
        //$objWriter->save($dir.'/demo.xlsx');

        //以下为需要用到IE时候设置
        // If you're serving to IE 9, then the following may be needed
        //header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        //header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        //header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        //header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        //header('Pragma: public'); // HTTP/1.0
        exit;
    }
}